﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Portal.Language;
using Portal.Environment;

namespace Portal.CDSP
{
    public class FSpecialSubjectBLL
    {
        public DataTable ReadingForGroupNews(string GroupNewsId)
        {
            string Query = "select * from TableGroupNewsItem,TableGroupNews where " +
                " TableGroupNews.groupnewsid = TableGroupNewsItem.fk_GroupNews_Id and " +
                " TableGroupNewsItem.fk_GroupNews_Id='" + GroupNewsId + "' and " +
                " TableGroupNewsItem.IsGroupNews = @IsGroupNews  and " +
                " fk_ParentGroupNewsItem_Id is null order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            int IsGroupNews = 1;
            sqlCommand.Parameters.Add("@IsGroupNews", SqlDbType.Int);
            sqlCommand.Parameters["@IsGroupNews"].Value = IsGroupNews;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }
        public DataTable ReadingForService(string belongSite, string ActiveLanguage)
        {
            string Query = "select Id=GroupNewsId,Subject=[" + ActiveLanguage + "Subject] from TableGroupNews where belongSite='" + belongSite + "'";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }


        public DataTable ReadingForServiceForRss(string belongSite, string ActiveLanguage)
        {
            string Query =
            " SELECT [GroupNewsItemId] " +
                " ,[fk_GroupNews_Id] " +
                " ,[GroupNewsItemName]" +
                " ,[IndexOrder]" +
                " ,[ShortGroupNewsItemId]" +
                " ,[RssUrl]" +
                " ,[JumpUrl]" +
                " ,[vieGroupNewsItemName]    " +
                " ,[AllowRss]" +
                " FROM [TableGroupNewsItem]" +
                " where AllowRss = 0 and Visible = 1 " +
                " and IsGroupNews = 1 " +
                " and ShowInMenu =1 " +
                " order by IndexOrder " +
                " belongSite='" + belongSite + "'";

            //string Query = "select Id=GroupNewsItemId,Subject=[" + ActiveLanguage + "GroupNewsItemName] from TableGroupNews where belongSite='" + belongSite + "'";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }



        public DataTable ReadingForServiceGroupNewsItem(string belongSite, string ActiveLanguage)
        {
            string Query = "select Id=GroupNewsItemId,Subject=TableGroupNews.[Subject] +' / '+ [GroupNewsItemName] from TableGroupNewsItem " +
                           "    join TableGroupNews on TableGroupNews.GroupNewsId = fk_GroupNews_Id" +
                           " where TableGroupNews.belongSite='" + belongSite + "' and TableGroupNews.IsNews = 1   and fk_ParentGroupNewsItem_Id is NULL";

            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingForServiceForPubAdmin(string belongSite, string ActiveLanguage)
        {
            string Query = "select Id=GroupNewsItemId,Subject=TableGroupNews.[Subject] +' / '+ [GroupNewsItemName] from TableGroupNewsItem,TableGroupNews where "+
                           " fk_ParentGroupNewsItem_Id is null "+
                           " and fk_GroupNews_Id = GroupNewsId "+
                            " and tablegroupnews.IsNews = 0 "+
                           " and TableGroupNews.belongSite='" + belongSite + "'";

            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }


        public DataTable ReadingForSearch(string belongSite, string ActiveLanguage)
        {
            string Query = "select Id=GroupNewsItemId,Subject=[GroupNewsItemName] from TableGroupNewsItem " +
                           "    join TableGroupNews on TableGroupNews.GroupNewsId = fk_GroupNews_Id" +
                           " where TableGroupNews.belongSite='" + belongSite + "' and GroupNewsItemId in (select fk_GroupNewsItem_Id from TableNews where belongSite='" + belongSite + "') order by GroupNewsId";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingForServiceGroupItem(string belongSite, string ActiveLanguage)
        {
            string Query = "select Id=GroupNewsItemId,Subject=TableGroupNews.[Subject] +' / '+ [GroupNewsItemName] from TableGroupNewsItem " +
                           "    join TableGroupNews on TableGroupNews.GroupNewsId = fk_GroupNews_Id" +
                           " where TableGroupNews.belongSite='" + belongSite + "' and TableGroupNewsItem.fk_ParentGroupNewsItem_Id is null order by GroupNewsId";
            //"select Id=GroupNewsItemId,Subject=[" + ActiveLanguage + "GroupNewsItemName] from TableGroupNewsItem"+
            //            "    join TableGroupNews on TableGroupNews.GroupNewsId = fk_GroupNews_Id" +
            //            "where belongSite='"+belongSite+"' and fk_ParentGroupNewsItem_Id is null";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }


        public DataTable ReadingMenuItems(string GroupNewsId, string belongSite)
        {

            string Query = "select * from TableGroupNewsItem,TableGroupNews where " +
                "TableGroupNews.groupnewsid = TableGroupNewsItem.fk_GroupNews_Id and " +
                "TableGroupNewsItem.fk_GroupNews_Id='" + GroupNewsId + "' and " +
                "TableGroupNewsItem.ShowInMenu = @ShowInMeNu  and " +
                "fk_ParentGroupNewsItem_Id is null " +
                "and TableGroupNews.belongSite = @belongSite " +
                "order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            int ShowInMenu = 1;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;

            sqlCommand.Parameters.Add("@ShowInMeNu", SqlDbType.Int);
            sqlCommand.Parameters["@ShowInMeNu"].Value = ShowInMenu;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingMenuItemsForSiteMap()
        {

            string Query = "select distinct a.Subject,a.GroupNewsId from TableGroupNews a,TableGroupNewsItem b where a.GroupNewsId = b.fk_GroupNews_Id ";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingOnlineSupport()
        {
            string Query = "select * from TableOnlineSupport";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingMenu(string GroupNewsId)
        {
            string Query = "select * from TableGroupNews where GroupNewsId='" + GroupNewsId + "'";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingGroupNewsItemName(string GroupNewsItemId)
        {
            string Query = "select * from TableGroupNewsItem where GroupNewsItemId ='" + GroupNewsItemId + "'";                       

            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingParentGroupNewsItemName(string fk_ParentGroupNewsItem_Id)
        {
            string Query = "select * from TableGroupNewsItem where fk_ParentGroupNewsItem_Id ='" + fk_ParentGroupNewsItem_Id + "'";

            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public static DataTable ReadingGroupNewsName(string GroupNewsItemId)
        {
            string Query = "select * from TableGroupNewsItem where GroupNewsItemId ='" + GroupNewsItemId + "'";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingSubMenuItems(string GroupNewsItemId)
        {
            string Query = "select * from TableGroupNewsItem where fk_ParentGroupNewsItem_Id='" + GroupNewsItemId + "' and IsGroupNews = 1 and ShowInMenu =1 order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingSubCatMenuItems(string GroupNewsItemId)
        {
            string Query = "select * from TableGroupNewsItem where fk_ParentGroupNewsItem_Id='" + GroupNewsItemId + "' and IsGroupNews = 1 and ShowInSubMenu =1 order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public string ReadingGroupParentIdForNewId(string NewsId)
        {
            string Query = "SELECT fk_GroupNews_Id,fk_GroupNewsItem_Id FROM TableNews where NewsId='" + NewsId + "' or ShortId ='" + NewsId + "'";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            string GroupNewsId = (string)dtResult.Rows[0]["fk_GroupNewsItem_Id"];
            return new FSpecialSubjectBLL().ReadingGroupItemsId(GroupNewsId);
        }

        public DataTable ReadingGroupParentId(string GroupNewsItemId)
        {
            string Query = "SELECT * FROM [TableGroupNewsItem] where (GroupNewsItemId ='" + GroupNewsItemId + "' or ShortGroupNewsItemId ='" + GroupNewsItemId + "')and fk_ParentGroupNewsItem_Id IS NULL";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public string ReadingGroupItemsId(string GroupNewsItemId)
        {
            string Query = "SELECT GroupNewsItemId,fk_ParentGroupNewsItem_Id FROM [TableGroupNewsItem] where GroupNewsItemId ='" + GroupNewsItemId + "' or ShortGroupNewsItemId ='" + GroupNewsItemId + "'";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            GroupNewsItemId = "";
            try
            {
                GroupNewsItemId = (string)dtResult.Rows[0]["fk_ParentGroupNewsItem_Id"];
            }
            catch { }
            if (GroupNewsItemId == "") GroupNewsItemId = (string)dtResult.Rows[0]["GroupNewsItemId"];
            return GroupNewsItemId;
        }

        public FSpecialSubjectCls CreateModelItem(string ShortGroupNewsItemId, string belongSite)
        {
            string Query = " select * from TableGroupNewsItem,tablegroupnews where " +
                            " TableGroupNewsItem.fk_groupnews_id =tablegroupnews.groupnewsid and  " +
                            " ShortGroupNewsItemId=@ShortGroupNewsItemId or " +
                            " GroupNewsItemId=@ShortGroupNewsItemId and " +
                            " tablegroupnews.belongSite = @belongSite";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;

            sqlCommand.Parameters.Add("@ShortGroupNewsItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@ShortGroupNewsItemId"].Value = ShortGroupNewsItemId;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;

            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            if (dtResult.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                FSpecialSubjectCls GroupNewsItemCls = new FSpecialSubjectCls();
                GroupNewsItemCls.GroupNewsItemId = (string)dtResult.Rows[0]["GroupNewsItemId"];
                GroupNewsItemCls.GroupNewsId = (string)dtResult.Rows[0]["fk_GroupNews_Id"];
                GroupNewsItemCls.GroupNewsItemName = "";
                try
                {
                    GroupNewsItemCls.GroupNewsItemName = (string)dtResult.Rows[0]["GroupNewsItemName"];
                }
                catch { }
                GroupNewsItemCls.IndexOrder = (int)dtResult.Rows[0]["IndexOrder"];
                GroupNewsItemCls.JumpUrl = (string)dtResult.Rows[0]["JumpUrl"];
                try
                {
                    GroupNewsItemCls.ParentGroupNewsItemId = (string)dtResult.Rows[0]["fk_ParentGroupNewsItem_Id"];
                }
                catch { }
                try
                {
                    GroupNewsItemCls.Htmlcontent = (string)dtResult.Rows[0]["Htmlcontent"];
                }
                catch { }
                try
                {
                    GroupNewsItemCls.Images = (string)dtResult.Rows[0]["GroupNewsItemImage"];
                }
                catch { }

                try
                {
                    GroupNewsItemCls.LargeImages = (string)dtResult.Rows[0]["GroupNewsItemLargeImage"];
                }
                catch { }
                try
                {
                    GroupNewsItemCls.Description = (string)dtResult.Rows[0][Environments.ActiveLanguage + "gDescription"];
                }
                catch { }

                try
                {
                    GroupNewsItemCls.AllowRss = (int)dtResult.Rows[0]["AllowRss"];
                }
                catch { GroupNewsItemCls.AllowRss = 0; }
                return GroupNewsItemCls;
            }
        }





        public FSpecialSubjectCls CreateModelGroup(string ShortGroupNewsItemId)
        {
            string Query = "select * from TableGroupNewsItem where ShortGroupNewsItemId=@ShortGroupNewsItemId or GroupNewsItemId=@ShortGroupNewsItemId ";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@ShortGroupNewsItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@ShortGroupNewsItemId"].Value = ShortGroupNewsItemId;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            if (dtResult.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                FSpecialSubjectCls GroupNewsItemCls = new FSpecialSubjectCls();

                GroupNewsItemCls.GroupNewsItemId = (string)dtResult.Rows[0]["GroupNewsItemId"];
                GroupNewsItemCls.GroupNewsId = (string)dtResult.Rows[0]["fk_GroupNews_Id"];
                GroupNewsItemCls.GroupNewsItemName = "";
                try
                {
                    GroupNewsItemCls.GroupNewsItemName = (string)dtResult.Rows[0]["GroupNewsItemName"];
                }
                catch { }
                GroupNewsItemCls.IndexOrder = (int)dtResult.Rows[0]["IndexOrder"];
                GroupNewsItemCls.JumpUrl = (string)dtResult.Rows[0]["JumpUrl"];
                try
                {
                    GroupNewsItemCls.ParentGroupNewsItemId = (string)dtResult.Rows[0]["fk_ParentGroupNewsItem_Id"];
                }
                catch { }
                try
                {
                    GroupNewsItemCls.Htmlcontent = (string)dtResult.Rows[0]["Htmlcontent"];
                }
                catch { }
                try
                {
                    GroupNewsItemCls.Images = (string)dtResult.Rows[0]["GroupNewsItemImage"];
                }
                catch { }

                try
                {
                    GroupNewsItemCls.LargeImages = (string)dtResult.Rows[0]["GroupNewsItemLargeImage"];
                }
                catch { }
                try
                {
                    GroupNewsItemCls.Description = (string)dtResult.Rows[0][Environments.ActiveLanguage + "gDescription"];
                }
                catch { }
                return GroupNewsItemCls;
            }
        }

        public FSpecialSubjectCls CreateModelItemAlias(string GroupNewsItemId)
        {
            string Query = "select * from TableGroupNewsItem where GroupNewsItemId=@GroupNewsItemId";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@GroupNewsItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupNewsItemId"].Value = GroupNewsItemId;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            if (dtResult.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                FSpecialSubjectCls GroupNewsItemCls = new FSpecialSubjectCls();

                GroupNewsItemCls.GroupNewsItemId = (string)dtResult.Rows[0]["GroupNewsItemId"];
                GroupNewsItemCls.GroupNewsId = (string)dtResult.Rows[0]["fk_GroupNews_Id"];
                GroupNewsItemCls.JumpUrl = (string)dtResult.Rows[0]["JumpUrl"];
                GroupNewsItemCls.GroupNewsItemName = "";
                try
                {
                    GroupNewsItemCls.GroupNewsItemName = (string)dtResult.Rows[0]["GroupNewsItemName"];
                }
                catch { }
                GroupNewsItemCls.IndexOrder = (int)dtResult.Rows[0]["IndexOrder"];
                try
                {
                    GroupNewsItemCls.ParentGroupNewsItemId = (string)dtResult.Rows[0]["fk_ParentGroupNewsItem_Id"];
                }
                catch { }

                try
                {
                    GroupNewsItemCls.Htmlcontent = (string)dtResult.Rows[0]["Htmlcontent"];
                }
                catch { }
                try
                {
                    GroupNewsItemCls.Images = (string)dtResult.Rows[0]["GroupNewsItemImage"];
                }
                catch { }
                try
                {
                    GroupNewsItemCls.LargeImages = (string)dtResult.Rows[0]["GroupNewsItemLargeImage"];
                }
                catch { }
                try
                {
                    GroupNewsItemCls.Description = (string)dtResult.Rows[0][Environments.ActiveLanguage + "gDescription"];
                }
                catch { }
                return GroupNewsItemCls;
            }
        }




        public DataTable ReadingNews(string GroupNewsItemId)
        {
            string Query = "select * from TableNews where fk_GroupNewsItem_Id='" + GroupNewsItemId + "' and IsPublishing=1 order by ShowOnTop DESC,EntryDate DESC";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        
        public DataTable ReadingNews_Hot(string GroupNewsId, int top, string belongSite)
        {
            string Query = "select TOP " + top + " * from TableNews where " +
                "fk_GroupNews_Id='" + GroupNewsId + "' and " +
                "belongSite = @belongSite and " +
                "IsPublishing=1 and ShowOnTop =1 order by EntryDate DESC";

            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;

            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingNews_Hot(int top, string belongSite)
        {
            string Query = " select TOP " + top + " * from TableNews where " +
                //"fk_GroupNews_Id='" + GroupNewsId + "' and " +
                " belongSite = @belongSite and " +
                " IsPublishing=1 and ShowOnTop =1 " +
                " order by EntryDate DESC ";

            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;

            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingNews_Lastest(string GroupNewsId, int top, string belongSite)
        {
            string Query = "select TOP " + top + " * from TableNews where " +
                //"fk_GroupNews_Id='" + GroupNewsId + "' and " +
                "belongSite = @belongSite and " +
                "IsPublishing=1 order by EntryDate DESC ";

            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;

            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingNews_ChuyenMuc(string GroupNewsItemId, int top, string belongSite)
        {
            string Query = "select TOP " + top + " * from TableNews " +
                "where fk_GroupNewsItem_Id='" + GroupNewsItemId + "' and " +
                "belongSite = @belongSite and " +
                "IsPublishing=1 order by ShowOnTop DESC,EntryDate DESC";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;

            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingNews_ChuyenMuc(
            string ParentGroupNewsItemId,
            string GroupNewsItemId, 
            int top, 
            string belongSite)
        {
            string Query =

                "   select TOP " + top + " * from TableNews " +
                "   where TableNews.fk_GroupNews_Id = @ParentGroupNewsItemId " +
                "   and TableNews.fk_GroupNewsItem_Id in " +
                "   (select GroupNewsItemId  from TableGroupNewsItem " +
                "    where fk_ParentGroupNewsItem_Id = @GroupNewsItemId " +
                "    )" +
                " and belongSite = @belongSite and " +
                " IsPublishing=1 order by EntryDate DESC" ;
                
                //" select TOP " + top + " * from TableNews,TableGroupNewsItem " +
                //" where fk_GroupNewsItem_Id='" + GroupNewsItemId + "' and " +
                //"   "+
                //" belongSite = @belongSite and " +
                //" IsPublishing=1 order by ShowOnTop DESC,EntryDate DESC";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;

            sqlCommand.Parameters.Add("@ParentGroupNewsItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@ParentGroupNewsItemId"].Value = ParentGroupNewsItemId;

            sqlCommand.Parameters.Add("@GroupNewsItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupNewsItemId"].Value = GroupNewsItemId;

            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingNews_ChuyenMuc2(string GroupNewsItemId, string GroupId, int top)
        {
            string Query = "select TOP " + top + " * from TableNews tn,TableGroupNewsItem tbni where tn.fk_groupnews_id = tbni.fk_GroupNews_Id and tbni.fk_GroupNews_Id = '" + GroupId + "' and  tn.fk_GroupNewsItem_Id='" + GroupNewsItemId + "' and IsPublishing=1 order by ShowOnTop DESC,EntryDate DESC";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingTopNews(string GroupNewsItemId, int top)
        {
            string Query = "select TOP " + top + " * from TableNews where fk_GroupNewsItem_Id='" + GroupNewsItemId + "' and IsPublishing=1 order by ShowOnTop DESC,TotalView DESC";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingTop1(string GroupNewsItemId)
        {
            string Query = "select * from TableNews where fk_GroupNewsItem_Id='" + GroupNewsItemId + "' and IsPublishing=1 order by ShowOnTop DESC,TotalView DESC";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }


        //public DataTable ReadingPartner(string GroupNewsItemId)
        //{
        //    string Query = "select * from TableNews where fk_GroupNewsItem_Id='" + GroupNewsItemId + "' and IsPublishing=1 order by ShowOnTop DESC,EntryDate DESC";
        //    SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
        //    sqlConnection.Open();
        //    SqlCommand sqlCommand = sqlConnection.CreateCommand();
        //    sqlCommand.CommandText = Query;
        //    DataTable dtResult = new DataTable();
        //    SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
        //    da.Fill(dtResult);
        //    return dtResult;
        //}

        public DataTable ReadingHtmlMenu(string GroupNewsItemId)
        {
            string Query = "select * from TableGroupNewsItem where GroupNewsItemId='" + GroupNewsItemId + "' and Htmlcontent is not null";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingNews(string GroupNewsItemId, int Top)
        {
            string Query = "select TOP " + Top + " * from TableNews where fk_GroupNewsItem_Id='" + GroupNewsItemId + "' and WorkflowStatus='Approved' and IsPublishing=1 order by ShowOnTop DESC,EntryDate DESC";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public string RewriteGetByCategoryUrl(string GetByCategoryUrl)
        {
            string reVal = GetByCategoryUrl;
            try
            {
                if (GetByCategoryUrl.Contains("-nc-"))
                {
                    reVal = GetByCategoryUrl.Replace("-nc-", "-nc2-");
                }
            }
            catch { }
            return reVal;
        }

        public DataTable ReadingMenuItems(string belongSite)
        {

            string Query = "select * from TableGroupNewsItem,TableGroupNews where " +
                "TableGroupNews.groupnewsid = TableGroupNewsItem.fk_GroupNews_Id and " +
                "TableGroupNewsItem.ShowInMenu = @ShowInMeNu  and " +
                "fk_ParentGroupNewsItem_Id is null " +
                "and TableGroupNews.belongSite = @belongSite " +
                "order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            int ShowInMenu = 1;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;

            sqlCommand.Parameters.Add("@ShowInMeNu", SqlDbType.Int);
            sqlCommand.Parameters["@ShowInMeNu"].Value = ShowInMenu;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

       



        public DataTable ReadingGroupForImgLinks(string GroupNewsItemId, string belongSite)
        {

            string Query = "select * from TableGroupNewsItem,TableGroupNews where " +
                "TableGroupNews.groupnewsid = TableGroupNewsItem.fk_GroupNews_Id and " +
                "TableGroupNewsItem.GroupNewsItemId='" + GroupNewsItemId + "' and " +
                "fk_ParentGroupNewsItem_Id is null " +
                "and TableGroupNews.belongSite = @belongSite " +
                "order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            int ShowInMenu = 1;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;

            sqlCommand.Parameters.Add("@ShowInMeNu", SqlDbType.Int);
            sqlCommand.Parameters["@ShowInMeNu"].Value = ShowInMenu;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingMenuItemsForSiteMap(string belongSite)
        {

            string Query = "select * from TableGroupNewsItem,TableGroupNews where " +
                 "TableGroupNews.groupnewsid = TableGroupNewsItem.fk_GroupNews_Id and " +
                //"TableGroupNewsItem.fk_GroupNews_Id='" + GroupNewsId + "' and " +
                //"TableGroupNewsItem.ShowInMenu = @ShowInMeNu  and " +
                 "fk_ParentGroupNewsItem_Id is null " +
                 "and TableGroupNews.belongSite = @belongSite " +
                 "order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            int ShowInMenu = 1;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;

            sqlCommand.Parameters.Add("@ShowInMeNu", SqlDbType.Int);
            sqlCommand.Parameters["@ShowInMeNu"].Value = ShowInMenu;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }           
    }
}
